/*
stat_weizhan_bd   微站数据统计
call pro_stat_jiance_data_hour ('2021-10-12 23:00:00');
call proc_stat_jiance_data_day	('2021-07-16 03:00:00');
call proc_stat_jiance_data_day	('2021-07-15 23:00:00');

pro_stat_jiance_data_hour	1、根据road_bd_hourdata 和 site_bd_hourdata 数据表，生成 t_d_jiance_hour 和 t_d_weizhan_hour
pro_stat_jiance_data_day	1、根据road_bd_daydata_cal 和 site_bd_daydata_cal 数据表，生成 t_d_jiance_day 和 t_d_weizhan_day

select * from t_d_weizhan_day;
select * from t_d_jiance_day;
select * from site_bd_daydata_cal;
select * from road_bd_daydata_cal;

		round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),2) as NOX 
*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `proc_stat_jiance_data_day`;
CREATE PROCEDURE `proc_stat_jiance_data_day`(in start_time_ varchar(40))
BEGIN
		DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
		DECLARE has_weizhan_data_ int DEFAULT 0;
		DECLARE has_site_data_ int DEFAULT 0;
		DECLARE reset_hour_start_ datetime DEFAULT now();
		DECLARE reset_hour_end_ datetime DEFAULT now();

		#DECLARE start_time_ datetime DEFAULT now();	
		if (SELECT DATE_FORMAT(start_time_,'%Y-%m-%d %H:%i:%s')) IS NULL then 
			set start_time_ = now();
		end if;
		set hour_start_ = start_time_;
		
		select DATE_FORMAT(start_time_,'%Y-%m-%d 00:00:00') into start_time_;
		set start_time_ = DATE_SUB(start_time_,INTERVAL 24 HOUR);
		set reset_hour_start_ = start_time_;
		set reset_hour_end_ =  DATE_ADD(reset_hour_start_,INTERVAL 24 HOUR);
		
		select 1 into has_weizhan_data_ from t_d_weizhan_day where PostTime=start_time_ limit 1;
		select 1 into has_site_data_ from t_d_jiance_day where PostTime=start_time_ limit 1;
		
		select start_time_,reset_hour_start_,reset_hour_end_,has_weizhan_data_,has_site_data_;
		
		IF has_weizhan_data_=0 and hour(hour_start_)>=3  THEN
				#清除当日小时数据，并重新写入
				delete from t_d_weizhan_hour where posttime>=reset_hour_start_ and posttime<reset_hour_end_;
				delete from t_d_jiance_hour where posttime>=reset_hour_start_ and posttime<reset_hour_end_;
				#重写小时数据
				select reset_hour_start_,reset_hour_end_,reset_hour_start_<reset_hour_end_ ;
				WHILE reset_hour_start_<reset_hour_end_ DO
					call proc_stat_jiance_data_hour(reset_hour_start_);
					set reset_hour_start_ = DATE_ADD(reset_hour_start_,INTERVAL 1 HOUR);
				END WHILE;
				select reset_hour_start_,reset_hour_end_,reset_hour_start_<reset_hour_end_ ;
				#微站数据行转列
				insert into t_d_weizhan_day (PostTime,MN,SiteName,lon,lat,PM25,PM10,O3,CO,`NO`,NO2,SO2) 
					select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
							#MAX(case r.pID when '01' then  AvgValue end) as tsp, 
							MAX(case r.pID when 'PM25' then  AvgValue end) as PM25,
							MAX(case r.pID when 'PM10' then  AvgValue end) as PM10,
							MAX(case r.pID when 'O3' then  AvgValue end) as O3,
							MAX(case r.pID when 'CO' then  AvgValue end) as CO,
							MAX(case r.pID when 'NO' then  AvgValue end) as `NO`,
							MAX(case r.pID when 'NO2' then  AvgValue end) as NO2,
							MAX(case r.pID when 'SO2' then  AvgValue end) as SO2		
							from road_bd_daydata_cal r right join 
								#(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d
								(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and devtype=4 and mn like 'SXYD0%') d
								on r.MN=d.MN and r.MN is not null 
							where r.PostTime=start_time_ and locate(r.pID,'01,PM25,PM10,O3,CO,NO,NO2,SO2') 
							group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
							order by r.PostTime asc,r.MN asc, r.pID asc; 
				# 更新NOX指标
				update t_d_weizhan_day set NOX=round((NO*2.05/1.34+NO2),2) where NOX is NULL and PostTime=start_time_;
		
		END IF;
		
-- 		select * from t_d_jiance_hour limit 100;
-- 		select * from site_bd_hourdata  limit 100;
-- 		select * from site_bd_device limit 100;
		IF has_site_data_=0 and hour(hour_start_)>=3  THEN
				# 监测点数据行转列
				insert into t_d_jiance_day (PostTime,MN,SiteName,lon,lat,tsp) 
					select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
							MAX(case r.pID when '01' then  AvgValue end) as tsp 
							from road_bd_daydata_cal r right join 
								#(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and ID in (select max(ID) from road_bd_device group by MN) ) d
								(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and devtype=4) d
								on r.MN=d.MN and r.MN is not null 
							where r.posttime>=start_time_ and r.posttime<reset_hour_end_ and locate(r.pID,'01')=1 
							group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
							order by r.PostTime asc,r.MN asc, r.pID asc; 
				# 监测点数据行转列
				insert into t_d_jiance_day (PostTime,MN,SiteName,lon,lat,tsp) 
					select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
							MAX(case r.pID when '01' then  AvgValue end) as tsp 
							from site_bd_daydata_cal r right join 
								#(select MN,LNG,LAT,ID,SiteName from site_bd_device bd where ID in (select max(ID) from site_bd_device group by MN) ) d
								(select MN,LNG,LAT,ID,SiteName from site_bd_device bd where devtype=4) d
								on r.MN=d.MN and r.MN is not null 
							where r.posttime>=start_time_ and r.posttime<reset_hour_end_ and locate(r.pID,'01')=1 
							group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
							order by r.PostTime asc,r.MN asc, r.pID asc; 
		END IF;
		

END
//
DELIMITER ;